﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cExcel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
using Ext.Net;
using ObjectLayer;
using System.Collections;
using System.Web;
using System.Data.SqlClient;

namespace AccessLayer
{
    public class RevenuesExcelReader
    {
        cExcel.Application app;
        cExcel.Workbook book;
        cExcel.Worksheet sheet;
        cExcel.Range rng;
        object valueMissing = System.Reflection.Missing.Value;
        int beginRow = 24;
        int beginCol=1;
        int capRow = 8;
        int timeRow = 10;
        Hashtable hashGroup;
        
        Hashtable hashCost=new Hashtable();
        public List<fact_costs_revenuesBO> lst;
        public void GetCode()
        {
            //if(Global.GlobalVariable.HashDimCostRevenueCode2==null)
            //{
                Global.GlobalVariable.HashDimCostRevenueCode2 = new Hashtable();
                Global.GlobalVariable.HashDimCostRevenueSlug = new Hashtable();
                List<dim_costs_revenuesBO> lst = dim_costs_revenuesDAO.GetAllC();
                foreach (dim_costs_revenuesBO item in lst)
                {
                    string code = item.code2;// +"-" + item.unit_code;
                    if (item.code2 != "")
                    {
                        if (Global.GlobalVariable.HashDimCostRevenueCode2[code] == null)
                        {
                            Global.GlobalVariable.HashDimCostRevenueCode2.Add(code, item);
                        }
                    }
                    else
                    {
                        if (Global.GlobalVariable.HashDimCostRevenueSlug[item.slug] == null)
                        {
                            Global.GlobalVariable.HashDimCostRevenueSlug.Add(item.slug, item);
                        }
                    }
                }
           // }
        }
        public void GetUnit()
        {
            //if (Global.GlobalVariable.HashUnit == null)
            //{
                Global.GlobalVariable.HashUnit = new Hashtable();
                List<dim_unitBO> lst = dim_unitDAO.GetAllC();
                foreach (dim_unitBO item in lst)
                {
                    if (item.code != "")
                        if (Global.GlobalVariable.UnitIndentity < int.Parse(item.code))
                            Global.GlobalVariable.UnitIndentity = int.Parse(item.code) + 1;
                    if (Global.GlobalVariable.HashUnit[item.slug] == null)
                    {
                        Global.GlobalVariable.HashUnit.Add(item.slug, item);
                    }
                }
            //}
        }
        public void GetCost(int timeKey,string branch_code)
        {
            List<fact_costs_revenuesBO> lst = fact_costs_revenuesDAO.GetByTimeKey(timeKey,branch_code);
            foreach (fact_costs_revenuesBO item in lst)
            {
                string key = timeKey + "-" + branch_code + "-" + item.cost_code + "-" + item.unit_code;
                if (hashCost[key] == null)
                {
                    hashCost.Add(key, item);
                }
            }
        }
        string CreateCell(int col, int row)
        {
            return ((char)col).ToString() + row.ToString();
        }
        public void Import(string path, DirectEventArgs e)
        {
            
            try
            {
                GetUnit();
                GetCode();
                hashGroup = new Hashtable();

                app = new cExcel.Application();
                book = app.Workbooks.Open(path, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing);
                sheet = (cExcel.Worksheet)book.Sheets[1];
                try
                {
                    int run = 1;
                    bool isMerg = false;
                    int unitCol = 0;
                    int codeCol = 0;
                    int costCol = 0;
                    int costMergCount=0;
                    for(int c=65;c<(int)'Z';c++)
                    {
                        for (int j = 1; j < 5; j++)
                        {
                            rng = sheet.get_Range(CreateCell(c, j), CreateCell(c, j));
                            if (rng.Value2 == null)
                                continue;
                            else
                            {
                                beginCol = c;
                                break;
                            }
                        }
                    }
                    while (true)
                    {
                        rng = sheet.get_Range(CreateCell(beginCol, run), CreateCell(beginCol, run));
                        run++;
                        if (rng.Value2 == null || rng.Value2.ToString().Replace(" ", "") == "")
                        {
                            continue;
                        }
                        if (Lib.StringProccess.RemoveUnicode(rng.Value2.ToString()).ToLower().Contains("thang"))
                        {
                            break;
                        }
                    }
                    beginRow = run + 1;
                    timeRow = run - 1;
                    //run = 
                    rng = sheet.get_Range(CreateCell(beginCol, run - 3), CreateCell(beginCol, run - 3));
                    if (!Lib.StringProccess.RemoveUnicode(rng.Value2.ToString().ToLower()).Replace(" ", "").Contains("doanhthu"))
                    {
                        e.Success = false;
                        e.ErrorMessage = "File không đúng định dạng!";
                        return;
                    }
                    while (true)
                    {
                        rng = sheet.get_Range(CreateCell(beginCol, run), CreateCell(beginCol, run));
                        run++;
                        if (rng.Value2 == null || rng.Value2.ToString().Replace(" ", "") == "")
                        {
                            continue;
                        }
                        if (rng.Value2.ToString()=="A")
                        {
                            beginRow = run;
                            break;
                        }
                    }
                    int colR = beginCol;
                    while (true)
                    {
                        rng = sheet.get_Range(CreateCell(colR, run-1), CreateCell(colR, run-1));
                        colR++;
                        if (rng.Value2 == null) continue;
                        if (rng.Value2.ToString() == "B") { unitCol = colR - 1; }
                        if (rng.Value2.ToString() == "C") { codeCol = colR - 1; }
                        if (rng.Value2.ToString() == "3") { costCol = colR - 1; costMergCount = rng.MergeArea.Count; break; }
                    }
                    
                    
                    int timekey = 0;
                    rng = sheet.get_Range(CreateCell(beginCol, timeRow), CreateCell(beginCol, timeRow));
                    string[] arrTime = rng.Value2.ToString().Split(' ');
                    //string[] arr = arrTime[arrTime.Length - 1].Split('/');
                    //Array.Reverse(arr);
                    string time = arrTime[5] + arrTime[1];
                    string slug = "";
                    GetCost(int.Parse(time),Global.GlobalVariable.UserInfo.branch_code);
                    int i=beginRow-1;
                    do 
                    {
                        string code = "";
                        i++;

                        rng = sheet.get_Range(CreateCell(costCol + 1, i), CreateCell(costCol + 1, i));
                        string check1 = rng.Value2 == null ? "" : rng.Value2.ToString();
                        rng = sheet.get_Range(CreateCell(costCol + 2, i), CreateCell(costCol + 2, i));
                        string check2 = rng.Value2 == null ? "" : rng.Value2.ToString();
                        //if (check1 == "" && (check2 == "K" || check2 == "10"))
                        //{
                        //    continue;
                        //}
                        string name = "";
                        string unitSlug = "";
                        string unitName="";
                        dim_unitBO objUnit = null;

                        rng = sheet.get_Range(CreateCell(unitCol, i), CreateCell(unitCol, i));
                        if(rng.Value2!=null && rng.Value2.ToString()!="")
                            unitName = rng.Value2.ToString();
                        if(rng.Value2!=null)
                            unitSlug = Global.GlobalMethod.CreateSlug(unitName).ToLower();
                        isMerg = (bool)rng.MergeCells;
                        rng = sheet.get_Range(CreateCell(beginCol + 5, i), CreateCell(beginCol + 5, i));
                        if ((rng.Value2 == null || rng.Value2.ToString() == ""))
                        {
                            if (isMerg) continue;
                            else break;
                        }
                        code = rng.Text.ToString();
                        rng = sheet.get_Range(CreateCell(beginCol, i), CreateCell(beginCol, i));
                        if (rng.Value2 != null)
                            name = rng.Value2.ToString();
                        slug = Global.GlobalMethod.CreateSlug(name);
                        
                        if(name=="" && unitSlug=="" && !isMerg)
                            continue;

                        //rng = sheet.get_Range(CreateCell(beginCol + 5, i), CreateCell(beginCol + 5, i));
                        //code = rng.Value2==null?"":rng.Value2.ToString();
                        //if (code == "") break;

                        rng = sheet.get_Range(CreateCell(costCol, i), CreateCell(costCol, i));
                        double cost = 0;
                        if (check1 == "" && (check2 == "K" || check2 == "10"))
                        {
                            cost = 0;
                        }
                        else
                        {
                            if ((bool)rng.MergeCells)
                            {
                                //if(rng.MergeArea.Count>costMergCount)
                                int col = costCol - (rng.MergeArea.Count-costMergCount);
                                rng.UnMerge();

                                rng = sheet.get_Range(CreateCell(col, i), CreateCell(col, i));
                            }
                            if (rng.Value2 != null && rng.Value2.ToString() != "")
                            {
                                cost = double.Parse(rng.Value2.ToString().Replace(",", ""));
                            }
                        }
                        string revCode = code;
                        dim_costs_revenuesBO objDim = (dim_costs_revenuesBO)Global.GlobalVariable.HashDimCostRevenueCode2[revCode];
                        if (objDim == null)
                        {
                            objDim = (dim_costs_revenuesBO)Global.GlobalVariable.HashDimCostRevenueSlug[slug];
                        }
                        //them tieu chi moi
                        if (objDim == null)
                        {
                            
                            objUnit = (dim_unitBO)Global.GlobalVariable.HashUnit[unitSlug];
                            // Them dom vi moi
                            if (objUnit == null)
                            {
                                objUnit = new dim_unitBO();
                                objUnit.code = Lib.CreateCode.CreateCode3("", Global.GlobalVariable.UnitIndentity.ToString());
                                objUnit.name = unitName;
                                objUnit.slug = unitSlug;
                                dim_unitDAO.InsertUpdateDelete(objUnit, ProviderAction.Insert);
                                Global.GlobalVariable.HashUnit.Add(objUnit.slug, objUnit);
                                Global.GlobalVariable.UnitIndentity++;
                               
                            }
                            objDim = new dim_costs_revenuesBO();
                            objDim.name = name;
                            objDim.unit_code = objUnit.code;
                            objDim.slug = Global.GlobalMethod.CreateSlug(name);
                            objDim.code = "R." + code;
                            objDim.code2 = code;
                            dim_costs_revenuesDAO.InsertUpdateDelete(objDim, ProviderAction.Insert);
                            if (objDim.code2 != "")
                                Global.GlobalVariable.HashDimCostRevenueCode2.Add(objDim.code2, objDim);
                            else
                                Global.GlobalVariable.HashDimCostRevenueSlug.Add(objDim.slug, objDim);
                        }
                        fact_costs_revenuesBO obj = new fact_costs_revenuesBO();
                        obj.cost_code = objDim.code;
                        obj.unit_code = objDim.unit_code;
                        obj.timekey = int.Parse(time);
                        obj.cost = cost;
                        obj.branch_code = Global.GlobalVariable.UserInfo.branch_code;
                        string key=time+"-"+Global.GlobalVariable.UserInfo.branch_code+"-"+objDim.code+"-"+objDim.unit_code;
                        
                        if (hashCost[key] == null)
                        {
                            fact_costs_revenuesDAO.InsertUpdateDelete(obj, ProviderAction.Insert);
                            hashCost.Add(key, obj);
                            hashGroup.Add(code+"-"+unitSlug, obj);
                        }
                        else //if (hashGroup[key]==null)
                        {
                            fact_costs_revenuesBO objGroup = (fact_costs_revenuesBO)hashGroup[code + "-" + unitSlug];
                            if (name == "")
                            {
                                if (objGroup != null)
                                    continue;
                            }
                            
                            if (objGroup != null)
                            {
                                obj.cost += objGroup.cost;
                                objGroup.cost = obj.cost;
                            }
                            fact_costs_revenuesDAO.InsertUpdateDelete(obj, ProviderAction.Update);
                           
                            if (objGroup == null)
                                hashGroup.Add(code + "-" + unitSlug, obj);
                            else
                            {
                                hashGroup.Remove(code + "-" + unitSlug);
                                hashGroup.Add(code + "-" + unitSlug, obj);
                            }
                        }
                        obj.criteria_name = objDim.name;
                        lst.Add(obj);
                        
                    } while (true);
                    Global.GlobalMethod.SuccsessMsg("Import thành công");

                }
                catch (System.Exception ex)
                {
                    Lib.Log.WriteLog("", ex.ToString());
                    Global.GlobalMethod.ErrorMsg("Lỗi hệ thống");
                    
                    //e.Success = false;
                    //e.ErrorMessage = "";
                }
                finally
                {
                    book.Close(false, valueMissing, valueMissing);
                    app.Quit();
                    File.Delete(path);
                }
            }
            catch (Exception ex)
            {
                Lib.Log.WriteLog("", ex.ToString());
                Global.GlobalMethod.ErrorMsg("Lỗi hệ thống");
                
                //e.Success = false;
                //e.ErrorMessage = "Lỗi hệ thống!\n" + ex.ToString();
            }
        }
        public void Optimize(string path, DirectEventArgs e)
        {
            try
            {
                GetCode();
                app = new cExcel.Application();
                book = app.Workbooks.Open(path, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing);
                sheet = (cExcel.Worksheet)book.Sheets[1];
                try
                {
                    rng = sheet.get_Range("B" + capRow, "B" + capRow);
                    if (!Lib.StringProccess.RemoveUnicode(rng.Value2.ToString().ToLower()).Replace(" ", "").Contains("chiphi"))
                    {
                        e.Success = false;
                        e.ErrorMessage = "File không đúng định dạng!";
                        return;
                    }
                    int timekey = 0;
                    rng = sheet.get_Range("B" + timeRow, "B" + timeRow);
                    string[] arrTime = rng.Value2.ToString().Split(' ');
                    string[] arr = arrTime[arrTime.Length - 1].Split('/');
                    Array.Reverse(arr);
                    string time = arr[0] + arr[1] + arr[2];
                    string slug = "";
                    int i = beginRow - 1;
                    do
                    {
                        i++;
                        rng = sheet.get_Range("B" + i, "B" + i);
                        if (rng.Value2 == null || rng.Value2.ToString() == "")
                        {
                            continue;
                        }
                        fact_costs_revenuesBO obj = new fact_costs_revenuesBO();

                        slug = Global.GlobalMethod.CreateSlug(rng.Value2.ToString());


                        rng = sheet.get_Range("C" + i, "C" + i);

                        string code = rng.Value2 == null ? "" : rng.Value2.ToString();

                        dim_costs_revenuesBO objDim = (dim_costs_revenuesBO)Global.GlobalVariable.HashDimCostRevenueCode2[code];
                        if (objDim != null)
                        {
                            rng = sheet.get_Range("B" + i, "B" + i);
                            objDim.slug = Global.GlobalMethod.CreateSlug(rng.Value2.ToString());
                            dim_costs_revenuesDAO.InsertUpdateDelete(objDim, ProviderAction.Update);
                                //Lib.StringProccess.RemoveUnicode(objDim.name).Replace(" ", "-").Replace("/", "-").Replace(".", "-");
                        }

                        //fact_costs_revenuesDAO.InsertUpdateDelete(obj, ProviderAction.Insert);

                    } while (true);


                }
                catch (System.Exception ex)
                {
                    e.Success = false;
                    e.ErrorMessage = "";
                }
                finally
                {
                    book.Close(false, valueMissing, valueMissing);
                    app.Quit();
                    File.Delete(path);
                }
            }
            catch (Exception ex)
            {
                e.Success = false;
                e.ErrorMessage = "Lỗi hệ thống!\n" + ex.ToString();
            }
        }
    }
}
